﻿Namespace Dimensions

    ''' <summary>Represents a Dimension</summary>
    ''' <remarks></remarks>
    Public Class MetisDimension
        Implements IMetisEntity

        Private _ID As Integer = -1
        Private _Name As String = ""
        Private _Table As DB.DBTable
        Private _Annotation As String = ""
        Private WithEvents _FieldList As New MetisDimensionFieldList(Me)

        Private _BackupName As String = ""
        Private _BackupAnnotation As String = ""
        Private _BackupFieldList As New MetisDimensionFieldList(Me)

        Private _UsedBy As Core.Functions.MetisFunctionList

        Private _IsModified As Boolean = False
        Private _LastUpdate As Date = Date.Now
        Private _CreatedOn As Date = Date.Now
        Private _ModifiedOn As Date = Date.Now
        Private _LoadState As MetisLoadState = MetisLoadState.NewDefinition

        Private _MetisCore As MetisCore

        ''' <summary>Raised when the definition has changed.</summary>
        ''' <param name="sender">The changed dimension</param>
        Public Event Changed(ByVal sender As IMetisEntity) Implements IMetisEntity.Changed
        ''' <summary>Raised when the definition is finally loaded</summary>
        ''' <param name="sender">The loaded dimension</param>
        Public Event Loaded(ByVal sender As IMetisEntity) Implements IMetisEntity.Loaded

        Public Event Progress(ByVal sender As IMetisEntity, ByVal Progress As MetisProgressEventArgs) Implements IMetisEntity.Progress
        ''' <summary>Raised when a property has changed.</summary>
        ''' <param name="sender">The dimension where a property has been changed.</param>
        ''' <param name="e">Encapsulates the name of the changed property.</param>
        Public Event PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged
        ''' <summary>Raised when the definition is finally saved.</summary>
        ''' <param name="sender">The saved dimension.</param>
        Public Event Saved(ByVal sender As IMetisEntity) Implements IMetisEntity.Saved

        Sub New(ByVal MetisCore As Core.MetisCore)
            _MetisCore = MetisCore
            _ID = _MetisCore.GetTemporaryID
            _Name = String.Format(My.Resources.Metis_Dim_NewName.ToString, _ID * -1)
            _BackupName = _Name

            _Table = New DB.DBTable(_MetisCore, "", DB.DBTableType.Table)

            Dim tmpIDfield As New MetisDimensionField(Me)
            tmpIDfield.Name = "ID"
            tmpIDfield.FieldType = MetisDimensionFieldType.KeyField
            tmpIDfield.DBFieldName = "ID"
            tmpIDfield.DBfieldTypeAsString = DB.DBField.FieldTypeToString(DB.DBFieldType.DBnumber)
            tmpIDfield.DBfieldNullable = False

            Dim tmpDescrField As New MetisDimensionField(Me)
            tmpDescrField.Name = "Description"
            tmpDescrField.FieldType = MetisDimensionFieldType.DescriptionField
            tmpDescrField.DBFieldName = "DESCRIPTION"
            tmpDescrField.DBfieldTypeAsString = DB.DBField.FieldTypeToString(DB.DBFieldType.DBvarchar)
            tmpDescrField.DBfieldLength = 64
            tmpDescrField.DBfieldNullable = False

            _FieldList.Add(tmpIDfield)
            _FieldList.Add(tmpDescrField)
            _UsedBy = New Core.Functions.MetisFunctionList(_MetisCore)
            _LoadState = MetisLoadState.NewDefinition
            _IsModified = True
        End Sub

        Sub New(ByVal MetisCore As Core.MetisCore, ByVal ID As Integer, ByVal Name As String, ByVal Annotation As String, ByVal Table As DB.DBTable, ByVal ModifiedOn As Date, ByVal CreatedOn As Date)
            _MetisCore = MetisCore
            _ID = ID
            _Name = Name

            _Annotation = Annotation
            _Table = Table
            _ModifiedOn = ModifiedOn
            _CreatedOn = CreatedOn

            _BackupName = _Name
            _BackupAnnotation = _Annotation
            _UsedBy = New Core.Functions.MetisFunctionList(_MetisCore)
            _LoadState = MetisLoadState.NotLoaded
            _IsModified = False
        End Sub

        Private Sub CheckIsModified()
            If (_Annotation <> _BackupAnnotation OrElse _Name <> _BackupName) Then
                _IsModified = True
                Exit Sub
            End If

            'new fields added?
            For Each item As MetisDimensionField In _FieldList
                If Not _BackupFieldList.Contains(item.ID) Then
                    _IsModified = True
                    Exit Sub
                End If
            Next

            'fields removed/changed?
            For Each item As MetisDimensionField In _BackupFieldList
                If Not _FieldList.Contains(item.ID) Then
                    _IsModified = True
                    Exit Sub
                Else
                    Dim tmpField As MetisDimensionField = _FieldList.FieldByID(item.ID)
                    If (item.Name <> tmpField.Name OrElse item.Annotation <> tmpField.Annotation OrElse _
                        item.DBfieldDefault <> tmpField.DBfieldDefault OrElse item.DBfieldLength <> tmpField.DBfieldLength OrElse _
                        item.DBFieldName <> tmpField.DBFieldName OrElse item.DBfieldNullable <> tmpField.DBfieldNullable OrElse _
                        item.DBfieldType <> tmpField.DBfieldType) Then
                        _IsModified = True
                        Exit Sub
                    End If
                End If
            Next

            _IsModified = False
        End Sub

        Sub HandleFieldsChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles _FieldList.ListChanged
            If _LoadState <> MetisLoadState.Loading Then
                CheckIsModified()
                RaiseEvent Changed(Me)
            End If
        End Sub

        Public Sub Reload() Implements IMetisEntity.Reload
            If _LoadState <> MetisLoadState.NewDefinition Then
                Dim OraReader As OracleDataReader
                Dim OraCmd As OracleCommand

                _LoadState = MetisLoadState.Loading

                'Dimension base information
                OraCmd = New OracleCommand(String.Format("SELECT name, table_name, description, modified_on, created_on " & _
                                                         "FROM dam_dimensions where dimension_id={0}", _ID), _MetisCore.Connection)
                OraReader = OraCmd.ExecuteReader
                If OraReader.Read Then
                    _Name = OraReader.GetString(0)
                    _Table = _MetisCore.DBTables.FindTable(OraReader.GetString(1))
                    If _Table Is Nothing Then _Table = New DB.DBTable(_MetisCore, OraReader.GetString(1), DB.DBTableType.Table)
                    _Annotation = If(OraReader.IsDBNull(2), "", OraReader.GetString(2))

                    _ModifiedOn = OraReader.GetDateTime(3)
                    _CreatedOn = OraReader.GetDateTime(4)

                    _BackupName = _Name
                    _BackupAnnotation = _Annotation

                    OraReader.Close()

                    'Fields
                    OraCmd = New OracleCommand(String.Format("SELECT dimension_field_id, field_name, field_type, db_field_name, description, " & _
                                                             "modified_on, created_on, number_format, NVL(column_width,64) " & _
                                                             "FROM dam_dimension_fields WHERE dimension_fk={0} ORDER BY seq", _ID), _MetisCore.Connection)
                    OraReader = OraCmd.ExecuteReader
                    _FieldList.Clear()
                    _BackupFieldList.Clear()
                    Do While OraReader.Read
                        Dim tmpFieldAnnotation = If(OraReader.IsDBNull(4), "", OraReader.GetString(4))
                        Dim tmpFieldNumFormat = If(OraReader.IsDBNull(7), "", OraReader.GetString(7))
                        Dim tmpField As New MetisDimensionField(Me, OraReader.GetInt32(0), OraReader.GetString(1), OraReader.GetInt32(2), _
                                                                  OraReader.GetString(3), tmpFieldAnnotation, OraReader.GetDateTime(5), OraReader.GetDateTime(6), _
                                                                  tmpFieldNumFormat, OraReader.GetInt32(8))
                        _FieldList.Add(tmpField)
                        _BackupFieldList.Add(tmpField.Clone)
                    Loop

                    'UsedBy
                    OraCmd = New OracleCommand(String.Format("SELECT DISTINCT function_fk FROM dam_function_fields " & _
                                                             "WHERE dimension_fk={0}", _ID), _MetisCore.Connection)
                    OraReader = OraCmd.ExecuteReader
                    _UsedBy.Clear()
                    Do While OraReader.Read
                        Dim tmpFunc As Core.Functions.MetisFunction = _MetisCore.Functions.Find(OraReader.GetInt32(0))
                        If tmpFunc IsNot Nothing Then _UsedBy.Add(tmpFunc)
                    Loop

                End If
                OraReader.Close()

                _LoadState = MetisLoadState.Loaded
                _IsModified = False
                _LastUpdate = Date.Now
                RaiseEvent Loaded(Me)
            End If

        End Sub

        Public Sub Save() Implements IMetisEntity.Save
            Dim OraCmd As OracleCommand

            'remove all unused table fields
            If _Table.FieldList.Count > 0 Then
                For i As Integer = _Table.FieldList.Count - 1 To 0
                    If Not Me._FieldList.ContainsDBField(_Table.FieldList(i).DBfieldName) Then
                        _Table.FieldList.RemoveAt(i)
                    End If
                Next
            End If

            'add new and update existing fields
            For Each item As MetisDimensionField In _FieldList
                If _Table.FieldList.ContainsFieldName(item.DBFieldName) Then
                    _Table.FieldList(item.DBFieldName) = item.DBField
                Else
                    _Table.FieldList.Add(item.DBField)
                End If
            Next


            Dim SQLscript As String = ""
            Dim sSetString As String = ""
            Dim tmpFieldID As Integer

            If _LoadState = MetisLoadState.NewDefinition Then
                _ID = _MetisCore.GetNextID
                _Table.TableName = "DIM_" & _ID

                SQLscript &= String.Format("INSERT INTO dam_dimensions (dimension_id, name, table_name, description) VALUES ({0}, '{1}', '{2}', '{3}');", _ID, _Name, _Table.TableName, _Annotation.Replace("'", "''")) & vbCrLf
                SQLscript &= String.Format("INSERT INTO dam_functions (function_id, name, table_name, func_type) VALUES ({0}, '{1}', '{2}', 0);", _ID, _Name, _Table.TableName) & vbCrLf

                tmpFieldID = _MetisCore.GetNextFieldID
                SQLscript &= String.Format("INSERT INTO dam_dimension_fields (dimension_field_id, field_name, db_field_name, dimension_fk, seq, description, field_type) " & _
                                           "VALUES ({0}, '{1}', '{2}', {3}, 0, '{4}', 1);", tmpFieldID, _FieldList(0).Name, _FieldList(0).DBFieldName, _ID, _FieldList(0).Annotation.Replace("'", "''")) & vbCrLf
                SQLscript &= String.Format("INSERT INTO dam_function_fields (function_field_id, field_name, db_field_name, function_fk, seq, field_type, dimension_fk) " & _
                                           "VALUES ({0}, '{1}', '{2}', {3}, 0, 1, {3});", tmpFieldID, _FieldList(0).Name, _FieldList(0).DBFieldName, _ID) & vbCrLf

                tmpFieldID = _MetisCore.GetNextFieldID
                SQLscript &= String.Format("INSERT INTO dam_dimension_fields (dimension_field_id, field_name, db_field_name, dimension_fk, seq, description, field_type) " & _
                                           "VALUES ({0}, '{1}', '{2}', {3}, 1, '{4}', 2);", tmpFieldID, _FieldList(1).Name, _FieldList(1).DBFieldName, _ID, _FieldList(1).Annotation.Replace("'", "''")) & vbCrLf
                SQLscript &= String.Format("INSERT INTO dam_function_fields (function_field_id, field_name, db_field_name, function_fk, seq, field_type) " & _
                                           "VALUES ({0}, '{1}', '{2}', {3}, 1, 0);", tmpFieldID, _FieldList(1).Name, _FieldList(1).DBFieldName, _ID) & vbCrLf

                SQLscript &= "COMMIT;" & vbCrLf

                SQLscript &= String.Format("EXECUTE IMMEDIATE 'ALTER TABLE {1} ADD CONSTRAINT DIM_{0}_PK PRIMARY KEY (id)';", _ID, _Table.TableName) & vbCrLf
                SQLscript &= String.Format("EXECUTE IMMEDIATE 'ALTER TABLE {1} ADD CONSTRAINT DIM_{0}_U01 UNIQUE (description)';", _ID, _Table.TableName) & vbCrLf
                SQLscript &= String.Format("EXECUTE IMMEDIATE 'CREATE SEQUENCE DIM_{0}_SEQ';", _ID) & vbCrLf
                SQLscript &= String.Format("EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER DIM_{0}_autoid " & _
                                                              "BEFORE INSERT ON DIM_{0} REFERENCING NEW AS New OLD AS Old FOR EACH ROW " & _
                                                              "BEGIN SELECT DIM_{0}_SEQ.NEXTVAL INTO :NEW.ID FROM dual; END;';", _ID) & vbCrLf


            ElseIf _Name <> _BackupName Or _
               _Annotation <> _BackupAnnotation Then

                sSetString = ""
                If _Name <> _BackupName Then sSetString &= ",name='" & _Name & "'"
                If _Annotation <> _BackupAnnotation Then sSetString &= ",description='" & _Annotation.Replace("'", "''") & "'"
                'If _Table.TableName <> _BackupTable.TableName Then sSetString &= ",table_name='" & _Table.TableName & "'"

                SQLscript &= String.Format("UPDATE dam_dimensions SET {1} " & "WHERE dimension_id={0};", _ID, sSetString.Substring(1)) & vbCrLf
                SQLscript &= String.Format("UPDATE dam_functions SET {1} " & "WHERE function_id={0};", _ID, sSetString.Substring(1)) & vbCrLf
            End If


            'Remove Fields that are requested to be removed
            For Each item As MetisDimensionField In _BackupFieldList
                If Not _FieldList.Contains(item.ID) Then
                    SQLscript &= String.Format("DELETE FROM dam_dimension_fields WHERE dimension_field_id={0};", item.ID) & vbCrLf
                End If
            Next

            'Add new Fields and update existing fields
            For Each item As MetisDimensionField In _FieldList
                If _BackupFieldList.Contains(item.ID) Then
                    sSetString = ""
                    Dim OldField As MetisDimensionField = _BackupFieldList.FieldByID(item.ID)
                    If item.Name <> OldField.Name Then sSetString &= ",field_name='" & item.Name & "'"
                    If item.Annotation <> OldField.Annotation Then sSetString &= ",description='" & item.Annotation.Replace("'", "''") & "'"
                    If _FieldList.IndexOf(item) <> _BackupFieldList.IndexOf(OldField) Then sSetString &= ",seq=" & _FieldList.IndexOf(item)

                    If sSetString <> "" Then SQLscript &= String.Format("UPDATE dam_dimension_fields SET {1} " & _
                                                                        "WHERE dimension_field_id={0};", item.ID, sSetString.Substring(1)) & vbCrLf
                    If sSetString <> "" Then SQLscript &= String.Format("UPDATE dam_function_fields SET {1} " & _
                                                                        "WHERE function_field_id={0};", item.ID, sSetString.Substring(1)) & vbCrLf
                ElseIf item.FieldType = MetisDimensionFieldType.ValueField Then
                    tmpFieldID = _MetisCore.GetNextFieldID
                    SQLscript &= String.Format("INSERT INTO dam_dimension_fields (dimension_field_id, field_name, db_field_name, dimension_fk, seq, description, field_type) " & _
                                               "VALUES ({0}, '{1}', '{2}', {3}, {4}, '{5}', 0);", tmpFieldID, item.Name, item.DBFieldName, _ID, _FieldList.IndexOf(item), item.Annotation.Replace("'", "''")) & vbCrLf
                    SQLscript &= String.Format("INSERT INTO dam_function_fields (function_field_id, field_name, db_field_name, function_fk, seq, field_type) " & _
                                               "VALUES ({0}, '{1}', '{2}', {3}, {4}, 0);", tmpFieldID, item.Name, item.DBFieldName, _ID, _FieldList.IndexOf(item)) & vbCrLf
                End If
            Next


            _Table.Save()

            If SQLscript <> "" Then
                SQLscript = String.Format("--- Save {1} ---{2}BEGIN{2}{0} COMMIT;{2}END;{2}", SQLscript, _Name, vbCrLf)
                OraCmd = New OracleCommand(SQLscript, _MetisCore.Connection)
                OraCmd.ExecuteNonQuery()
            End If

            RaiseEvent Saved(Me)
            _LoadState = MetisLoadState.NotLoaded
            Reload()
            If _MetisCore.Dimensions.Find(_ID) Is Nothing Then
                _MetisCore.Dimensions.Add(Me)
            End If
            RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs("Name"))
        End Sub

        Public Sub SaveFormat()
            If _LoadState <> MetisLoadState.NewDefinition Then

                Dim OraCmd As OracleCommand
                Dim sSetString As String
                Dim SQLscript As String = ""

                For Each item As MetisDimensionField In _FieldList
                    Dim OldField As MetisDimensionField = _BackupFieldList.FieldByID(item.ID)
                    If OldField IsNot Nothing Then
                        sSetString = ""

                        If item.NumberFormat <> OldField.NumberFormat Then sSetString &= ",number_format='" & item.NumberFormat & "'"
                        If item.ColumnWidth <> OldField.ColumnWidth Then sSetString &= ",column_width=" & item.ColumnWidth

                        If sSetString <> "" Then SQLscript &= String.Format("UPDATE dam_dimension_fields SET {1} " & _
                                                                            "WHERE dimension_field_id={0};", item.ID, sSetString.Substring(1)) & vbLf
                    End If
                Next

                If SQLscript <> "" Then
                    OraCmd = New OracleCommand(String.Format("--- Save Format {1} --- {2}BEGIN {0} COMMIT; END;{2}", SQLscript, _Name, vbCrLf), _MetisCore.Connection)
                    OraCmd.ExecuteNonQuery()
                End If

            End If
        End Sub

        Public Sub Drop()
            Dim OraCmd As OracleCommand
            Dim SQL As String
            If _LoadState <> MetisLoadState.NewDefinition Then
                SQL = String.Format("EXECUTE IMMEDIATE 'DROP TABLE {0} CASCADE CONSTRAINTS';" & _
                                    "DELETE FROM dam_dimensions WHERE dimension_id={1};" & _
                                    "DELETE FROM dam_functions WHERE function_id={1};", _Table.TableName, _ID)
                OraCmd = New OracleCommand(String.Format("BEGIN {0} END;", SQL), _MetisCore.Connection)
                OraCmd.ExecuteNonQuery()
                _MetisCore.Dimensions.Remove(Me)
            End If

        End Sub

        Public Overrides Function Equals(ByVal obj As Object) As Boolean
            If TypeOf obj Is MetisDimension AndAlso DirectCast(obj, MetisDimension).ID = _ID Then
                Return True
            Else
                Return MyBase.Equals(obj)
            End If
        End Function

        Public ReadOnly Property MetisCore() As MetisCore
            Get
                Return _MetisCore
            End Get
        End Property

        Public ReadOnly Property ID() As Integer Implements IMetisCommonEntity.ID
            Get
                Return _ID
            End Get
        End Property

        Public Property Name() As String Implements IMetisCommonEntity.Name
            Get
                Return _Name
            End Get
            Set(ByVal value As String)
                If _LoadState = MetisLoadState.NotLoaded Then Reload()
                If MetisCore.IsValidFieldName(value, 64) Then
                    For Each item As MetisDimension In _MetisCore.Dimensions
                        If Not Me.Equals(item) AndAlso value.Equals(item.Name, StringComparison.OrdinalIgnoreCase) Then
                            Throw New Exceptions.DublicateNameException(value)
                        End If
                    Next
                    _Name = value
                    RaiseEvent Changed(Me)
                    RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs("Name"))
                    CheckIsModified()
                Else
                    Throw New Exceptions.InvalidNameException(value)
                End If
            End Set
        End Property

        Public Property Annotation() As String Implements IMetisCommonEntity.Annotation
            Get
                Return _Annotation
            End Get
            Set(ByVal value As String)
                If _LoadState = MetisLoadState.NotLoaded Then Reload()
                _Annotation = value
                RaiseEvent Changed(Me)
                RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs("Annotation"))
                CheckIsModified()
            End Set
        End Property

        Public ReadOnly Property Fields() As MetisDimensionFieldList
            Get
                If _LoadState = MetisLoadState.NotLoaded Then Reload()
                Return _FieldList
            End Get
        End Property

        Public Property Table() As DB.DBTable
            Get
                Return _Table
            End Get
            Set(ByVal value As DB.DBTable)
                If _LoadState = MetisLoadState.NotLoaded Then Reload()
                _Table = value
                RaiseEvent Changed(Me)
                RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs("Table"))
                CheckIsModified()
            End Set
        End Property

        Public ReadOnly Property UsedBy() As Functions.MetisFunctionList
            Get
                If _LoadState = MetisLoadState.NotLoaded Then Reload()
                Return _UsedBy
            End Get
        End Property

        Public ReadOnly Property IsModified() As Boolean Implements IMetisEntity.IsModified
            Get
                Return _IsModified
            End Get
        End Property

        Public ReadOnly Property LastUpdate() As Date Implements IMetisEntity.LastUpdate
            Get
                Return _LastUpdate
            End Get
        End Property

        Public ReadOnly Property LoadState() As MetisLoadState Implements IMetisEntity.LoadState
            Get
                Return _LoadState
            End Get
        End Property

        Public ReadOnly Property CreatedOn() As Date Implements IMetisEntity.CreatedOn
            Get
                Return _CreatedOn
            End Get
        End Property

        Public ReadOnly Property ModifiedOn() As Date Implements IMetisEntity.ModifiedOn
            Get
                Return _ModifiedOn
            End Get
        End Property

        Public Function CompareTo(ByVal other As IMetisCommonEntity) As Integer Implements System.IComparable(Of IMetisCommonEntity).CompareTo
            Return String.Compare(_Name, other.Name, True)
        End Function

        Public Overrides Function ToString() As String
            Return _Name
        End Function

        Public Function GetNextID() As Integer Implements IMetisEntity.GetNextID
            _ID = _MetisCore.GetNextID
            RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs("ID"))
            Return _ID
        End Function
    End Class
End Namespace
